اما وقتی شما یک ایندکس طراحی می کنید باید به محدودیت اندازه ایندکس، توجه داشته باشید. به ویژه هنگامی که ایندکس شما شامل تعداد زیادی ستون های کلید یا ستون های با اندازه بزرگ است. البته در SQL Server نسخه 2016 اندازه ایندکس نسبت به نسخه های قبلی افزایش یافته است که امکان انعطاف پذیری بیشتر به ما می دهد ولی این محدودیت همچنان وجود دارد.
در SQL Server دو نوع ایندکس داریم:
1- Clustered Index: در این نوع ایندکس، اطلاعات سطرهای جدول بصورت فیزیکی روی دیسک مرتب و ذخیره می شود. به همین علت برای هر جدول فقط می توان یک ایندکس از نوع Clustered داشت.
2- Non-Clustered Index: هر ایندکس از این نوع، نسخه مرتب شده از ستون های کلید است که به همراه یک اشاره گر به ردیف فیزیکی اشاره می کند. محدودیتی برای تعداد ایندکس های Non-Clustered برای یک جدول وجود ندارد.
هر چند تعداد ایندکس های Non-Clustered محدودیت ندارد ولی اضافه کردن تعداد زیادی از ایندکس های Non-Clustered برای جدول می تواند کارایی را بدتر کند. این ایندکس ها فضای دیسک را مصرف خواهند کرد و به دلیل نیاز به بروزرسانی ایندکس ها در زمان عملیات INSERT/DELETE/UPDATE ، سرعت اجرای عملیات روی جدول را کاهش خواهد داد.
به دلیل محدودیت اندازه مجاز برای ستون های کلید، تصمیم گیری اینکه از کدام ستون کلید برای ایندکس Non-Clustered استفاده کنید کار آسانی نیست. جهت بررسی اندازه ایندکس، آزمایش واقعی با جدولی با 5 ستون انجام می دهیم:
CREATE TABLE IndexSizeTest
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
Name CHAR(200),
Phone CHAR(200),
Address CHAR(1300),
PostCode CHAR(500)
)
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
Name CHAR(200),
Phone CHAR(200),
Address CHAR(1300),
PostCode CHAR(500)
)
پس از ایجاد جدول 1000 رکورد درج می کنیم:
INSERT INTO IndexSizeTese VALUES('John', ' 123456123456', 'Washington DC', '7070')
GO 1000
GO 1000
حال برای محاسبه اندازه ایندکس در SQL Server نسخه 2014 از دستور زیر استفاده می کنیم:
SELECT SUM(max_length) as IndexSize
FROM sys.Column
WHERE Name IN (N'Name', N'Phone', N'PostCode') AND object_id = OBJECT_ID(N'dbo.IndexSizeTest')
FROM sys.Column
WHERE Name IN (N'Name', N'Phone', N'PostCode') AND object_id = OBJECT_ID(N'dbo.IndexSizeTest')
نتیجه 900 بایت خواهد بود.
با دستور زیر می توانیم ایندکس Non-Clustered برای ستون های فوق الذکر بسازیم:
CREATE NONCLUSTERED INDEX IX_Test1 ON IndexSizeTest(Name, Phone, PostCode)
اندازه ستون های Name و Phone و Address برابر با 1700 بایت خواهد بود. اگر بخواهیم در SQL Server نسخه 2014 برای این ستون ها ایندکس از نوع Non-Clustered بسازیم با خطایی مواجه خواهیم شد که اعلام می کند اندازه ماکزیمم 900 بایت است ولی اندازه ایندکس ستون های انتخاب شده 1700 بایت است.
حال همین مراحل را در SQL Server نسخه 2016 تکرار کنید. ایندکس برای ستون های Name و Phone و Address ایجاد خواهد شد. در ادامه تلاش می کنیم که برای ستون های Name و Phone و Address و PostCode ایندکس بسازیم. پیام خطایی مشاهده خواهیم کرد که اندازه ایندکس 2200 بایت است در حالی که حداکثر اندازه مجاز ایندکس 1700 بایت است.
هر چند حداکثر اندازه مجاز برای ایندکس در نسخه 2016 نسبت به نسخه های قبلی افزایش داشته است ولی همچنان این محدودیت برقرار است. ضمن اینکه علاوه بر محدودیت حداکثر اندازه مجاز، محدودیت حداکثر تعداد ستون هایی که می توانند در ایندکس شرکت داشته باشند نیز وجود دارد که تعداد آن حداکثر 16 ستون است. به همین دلیل در زمان ایجاد ایندکس، ستون های غیرکلید که در ایندکس نیاز داریم را بصورت Include فراخوانی می کنیم. زیرا ستون های Include شده در محدودیت ها محاسبه نمی شوند. لذا دستور ایجاد ایندکس بصورت زیر خواهد بود:
CREATE NONCLUSTERED INDEX IX_Test2 (Name, Phone, PostCode) INCLUDE (Address)
سید حامد واحدی
13 مرداد 1396